const Router = require('koa-router');
const router = new Router();
const tools = require('../../tools/tool')
const {db, QueryTypes} = require("../../models/db");

// 库存统计
router.post('/stock/kctj', async ctx => {
    let formData = ctx.request.body.formData;
    let tenantId = ctx.user.tenantId
    let excel = ctx.request.body.excel
    let beginDate = formData.beginDate ? formData.beginDate : '2001-01-01'
    let endDate = formData.endDate ?formData.endDate:tools.getDay()
    let goodCatCode = await tools.getGoodCode(ctx)

    let invName = formData.invName?formData.invName:'';
    let sql =  `
                select tenantId, invId, invName, spec, locationId, locationName, unit,unit2,unit1,hjName,goodCatCode,goodCatName,
                       initC, initP, initM,  inC, inM,  outC, outBaseQty,outM,  lastC,lastBaseQty, lastM 
                from (
                        select a.tenantId,
                             a.invId,
                             g.name               invName, g.spec, g.unit1,g.unit2,g.hjName,g.goodCatName,goodCatCode,
                             a.locationId,
                             s.name               locationName,
                             a.unit,
                               round(initC/spec,2) initC,
                             initP,
                             initM,
                               round(inC/spec,2) inC,
                             inM,
                               round(outC/spec,2) outC,
                               outC outBaseQty,
                             outM,
                             round((initC + inC - outC)/spec,2) lastC,
                               (initC + inC - outC) lastBaseQty,
                             (initM + inM - outM) lastM
                        from (
                                select a.*,
                                       isnull(b.inC, 0)  inC,
                                       isnull(b.inM, 0)  inM,
                                       isnull(b.outC, 0) outC,
                                       isnull(b.outM, 0) outM
                                from (
                                       SELECT a.tenantId,
                                             a.locationId,
                                             a.invId,
                                             a.unit,
                                             lastCount           initC,
                                             ROUND(lastPrice, 2) initP,
                                             lastMoney           initM
                                       from ( SELECT * from inventory_detail where tenantId = ${tenantId} and billDate < '${beginDate}' ) a
                                       inner join
                                           (
                                               SELECT max(seq) seq, tenantId, locationId, invId
                                                from inventory_detail
                                                where tenantId = ${tenantId}
                                                  and billDate < '${beginDate}'
                                                GROUP BY tenantId, locationId, invId
                                            ) b
                                           on a.tenantId = b.tenantId and a.locationId = b.locationId and a.invId = b.invId and a.seq = b.seq
                                      ) a
                                left join
                                     (
                                        SELECT tenantId,
                                             locationId,
                                             invId,
                                             sum(inCount)  inC,
                                             sum(inMoney)  inM,
                                             sum(outCount) outC,
                                             sum(costMoney) outM
                                          from inventory_detail
                                          where tenantId = ${tenantId} and billDate >= '${beginDate}' and billDate <= '${endDate}' GROUP BY tenantId, locationId, invId
                                     ) b
                                     on a.tenantId = b.tenantId and a.locationId = b.locationId and a.invId = b.invId
                            ) a
                               inner join v_goods g on a.invId = g.id
                               left join storage s on a.locationId = s.id
                      ) a where tenantId=${tenantId}  
            `
    if(invName) sql += ` and invName like '%${invName}%' `
    if(formData.locationId) sql += ' and locationId='+formData.locationId
    if(goodCatCode) {
        sql += `  and goodCatCode like '${goodCatCode}%' `
    }
    // if(!formData.lastC) {
    //     sql += ` and lastC <> 0 `
    // }
    // if(!formData.bizC) {
    //     sql += ` and (inC <> 0 or outC <> 0) `
    // }
    let sort = tools.getSort(ctx)
    let sorts = ' tenantId,locationId,goodCatName,invName '
    if(sort) sorts = sort
    sql += ` order by ${sorts}  `
    let list = await db.query(sql, {type: QueryTypes.SELECT})
    list.map(row => {
        row.initM = row.initM ? row.initM.toFixed(2):''
        row.inM = row.inM ? row.inM.toFixed(2):''
        row.outM = row.outM ? row.outM.toFixed(2):''
        row.lastM = row.lastM ? row.lastM.toFixed(2):''
        row.lastPrice = row.lastC ? tools.xe.round(tools.xe.divide(row.lastM, row.lastC),2) : ''
    })
    if(excel) {
        return tools.doExcelOut(ctx, list)
    }
    return ctx.suc('', {
        total: 0,
        list
    })
})

module.exports = router;
